package com.zl.autocode.controller.generator;


import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.zl.autocode.controller.base.AutocodeBaseController;
import com.zl.autocode.dto.generator.ReqSqlQueryDTO;
import com.zl.autocode.dto.generator.ResSqlQueryDTO;
import com.zl.autocode.entity.autocode.ZlProject;
import com.zl.autocode.service.generator.IGeneratorSelectService;
import com.zl.autocode.service.generator.IGeneratorSqlService;
import com.zl.common.aop.LogSimpleResult;
import com.zl.common.dto.ReqDTO;
import com.zl.common.dto.ResDTO;
import com.zl.common.enums.resp.GlobalRespEnum;
import com.zl.common.exception.BizException;

import cn.hutool.db.sql.SqlUtil;
import io.swagger.annotations.Api;

/**
 *  
 * @author ZL
 */
@Api(value="在线SQL")
@Controller("autocodeGeneratorSqlController")
@RequestMapping("/autocode/diysql")
public class GeneratorSqlController extends AutocodeBaseController {
    
    @Autowired
    private IGeneratorSqlService generatorSqlService;
    @Autowired
    private IGeneratorSelectService generatorSelectService;
    /**
     * 在线SQL查询
     */
    @RequestMapping(value="/list", method = {RequestMethod.POST})
    @ResponseBody
    @LogSimpleResult
    public ResDTO<ResSqlQueryDTO> querySqlResult(@RequestBody ReqDTO<ReqSqlQueryDTO> reqInfo) throws Exception{
        ReqSqlQueryDTO reqData = reqInfo.getReqData();
        
        Pattern RULE = Pattern.compile("\\b(exec|execute|create|insert|delete|update|drop|chr|mid|master|truncate|" +
                "char|declare|sitename|net user|xp_cmdshell|grant|use|group_concat|column_name|" +
                "information_schema.columns|table_schema|chr|mid|master|truncate|--|//|/)\\b",Pattern.CASE_INSENSITIVE);
        
        if(StringUtils.isBlank(reqData.getSqlStr())){
            throw new BizException(GlobalRespEnum.DATABASE_CONNECT_FAILED);
        }
        
        Matcher sqlm = RULE.matcher(reqData.getSqlStr());
        if(sqlm.find()){
            throw new BizException(GlobalRespEnum.DATABASE_CONNECT_FAILED);
        }
        
        
        //获取数据库信息
        ZlProject projectInfo = generatorSelectService.queryProject(reqData.getProjectId());
        
        ResSqlQueryDTO resDTO = generatorSqlService.querySqlResult(reqInfo, projectInfo);
        return successDATA(resDTO);
    }
    
    /**
     * 格式化SQL语句
     */
    @LogSimpleResult
    @ResponseBody
    @RequestMapping(value="/sqlFormat", method = RequestMethod.POST)
    public ResDTO<ResSqlQueryDTO> sqlFormat(@RequestBody ReqDTO<ReqSqlQueryDTO> reqInfo) throws Exception{
        ReqSqlQueryDTO reqData = reqInfo.getReqData();
        
        ResSqlQueryDTO resDTO = new ResSqlQueryDTO();
        
        ZlProject projectInfo = generatorSelectService.queryProject(reqData.getProjectId());
        String databaseType = projectInfo.getDatabaseType();
        String sourceString = reqData.getSqlStr();
        if(StringUtils.isNotBlank(sourceString)){
            sourceString = SqlUtil.formatSql(sourceString);
            resDTO.setSqlStr(sourceString);
        }
        
        return successDATA(resDTO);
    }
}